Create Date Table , Measure And Calculated Column
8
1. Creating a Date Table:
Date = ADDCOLUMNS(
CALENDAR( MIN('Opportunities'[NewFormatDate]),
MAX( 'Opportunities'[NewFormatDate])
),
"Year", YEAR( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q") ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q") ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Week Day Number", WEEKDAY ( [Date] ),
"Week Day", FORMAT ( [Date], "dddd" ),
"Year Month Number", YEAR ( [Date] ) *100 + MONTH ( [Date] ),
"Year Month", FORMAT ( [Date], "mmm" ) & " " & YEAR ( [Date] ),
"Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q") ),
"Year Quarter", "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( [Date] )
)
Follow the GIF to create the date table

Connect the DateTable to the opportunities Table with the date column
And the SalesRep Table to Rep table using Rep column
You will follow below Screen shots to Create your measures
create on opportunity Table
- .Deal won =
CALCULATE (
COUNT ( 'Opportunities'[Statusid]),
FILTER (Opportunities,Opportunities[Statusid] = 5 ))
2 . WinCount = COUNT(Opportunities[Statusid])
3. Win Rate = DIVIDE([.Deal won],Opportunities[.WinCount])
4. .New Win Rate = IF(ISBLANK([Win Rate]),0,[Win Rate])
5. YoY% =
VAR __PREV_YEAR = CALCULATE([Win Rate], DATEADD('Date'[Date], -1, YEAR))
RETURN
DIVIDE([Win Rate] - __PREV_YEAR, __PREV_YEAR)
6. YOY = SWITCH(TRUE(),[YoY%]>0,UNICHAR(9650),[YoY%] <0,UNICHAR(9660)) & ROUND ( [YoY%], 2) * 100 &"%"
7. .KPIPerformanceIndicatorColor = SWITCH(TRUE(),[YoY%]>0,"#1AAB40",[YoY%] <0,"#8B0000",[YoY%]=0, "#3a6c9b")
8. .Deal Lost Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=10))
9. .Deal won Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=5))
10. .TOTal Reve = SUM(Opportunities[Revenue])
11. .% Loss Revenue = DIVIDE( [.Deal Lost Rev], [.TOTal Reve], 0 )
12. .% won Revenue = DIVIDE( [.Deal won Rev], [.TOTal Reve], 0 )
The Gif to create measures
Calculated Column
.Closed Revenue = if(Opportunities[Statusid]= 5,Opportunities[Revenue], BLANK())
.New Revenue = IF(ISBLANK(Opportunities[.Closed Revenue]),(0),Opportunities[.Closed Revenue])
.Lost Revenue = IF(Opportunities[Statusid] = 10, Opportunities[Revenue], 0)
For more information about the date table, please see the YouTube below.
https://www.youtube.com/watch?v=-li7sxUxEqA
Create Date Table , Measure And Calculated Column
8
1. Creating a Date Table:
Date = ADDCOLUMNS(
CALENDAR( MIN('Opportunities'[NewFormatDate]),
MAX( 'Opportunities'[NewFormatDate])
),
"Year", YEAR( [Date] ),
"Quarter Number", INT ( FORMAT ( [Date], "q") ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q") ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Week Day Number", WEEKDAY ( [Date] ),
"Week Day", FORMAT ( [Date], "dddd" ),
"Year Month Number", YEAR ( [Date] ) *100 + MONTH ( [Date] ),
"Year Month", FORMAT ( [Date], "mmm" ) & " " & YEAR ( [Date] ),
"Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q") ),
"Year Quarter", "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( [Date] )
)
Follow the GIF to create the date table

Connect the DateTable to the opportunities Table with the date column
And the SalesRep Table to Rep table using Rep column
You will follow below Screen shots to Create your measures
create on opportunity Table
- .Deal won =
CALCULATE (
COUNT ( 'Opportunities'[Statusid]),
FILTER (Opportunities,Opportunities[Statusid] = 5 ))
2 . WinCount = COUNT(Opportunities[Statusid])
3. Win Rate = DIVIDE([.Deal won],Opportunities[.WinCount])
4. .New Win Rate = IF(ISBLANK([Win Rate]),0,[Win Rate])
5. YoY% =
VAR __PREV_YEAR = CALCULATE([Win Rate], DATEADD('Date'[Date], -1, YEAR))
RETURN
DIVIDE([Win Rate] - __PREV_YEAR, __PREV_YEAR)
6. YOY = SWITCH(TRUE(),[YoY%]>0,UNICHAR(9650),[YoY%] <0,UNICHAR(9660)) & ROUND ( [YoY%], 2) * 100 &"%"
7. .KPIPerformanceIndicatorColor = SWITCH(TRUE(),[YoY%]>0,"#1AAB40",[YoY%] <0,"#8B0000",[YoY%]=0, "#3a6c9b")
8. .Deal Lost Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=10))
9. .Deal won Rev = CALCULATE (SUM( Opportunities[Revenue] ), FILTER (Opportunities,Opportunities[Statusid]=5))
10. .TOTal Reve = SUM(Opportunities[Revenue])
11. .% Loss Revenue = DIVIDE( [.Deal Lost Rev], [.TOTal Reve], 0 )
12. .% won Revenue = DIVIDE( [.Deal won Rev], [.TOTal Reve], 0 )
The Gif to create measures
Calculated Column
.Closed Revenue = if(Opportunities[Statusid]= 5,Opportunities[Revenue], BLANK())
.New Revenue = IF(ISBLANK(Opportunities[.Closed Revenue]),(0),Opportunities[.Closed Revenue])
.Lost Revenue = IF(Opportunities[Statusid] = 10, Opportunities[Revenue], 0)
For more information about the date table, please see the YouTube below.
https://www.youtube.com/watch?v=-li7sxUxEqA